using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Data.Common;
using System.Data;
using System.Globalization;
using System.ComponentModel;

namespace ResourceBlender_Express {
    /// <summary>
    /// A window for managing elements and resources used by an application
    /// </summary>
    public partial class WinResourcesGrid : Window {
        private Database db;
        private ObservableSortedDictionary<string, string> bundles,
                                                           languages;
        private SortedDictionary<int, string> resourceCache;
        private string resourceSql, // the 'xxx - snippet...' selection query for generating the resource cache
                       crossTabColumns, // the field selection part of the cross-tab query. Saves writing the same huge switch on both forms.
                       crossTabSql; // the whole crosstab query
        private RBApplication application;

        /// <summary>
        /// Initializes a new <see cref="WinResourcesGrid"/>.
        /// </summary>
        /// <param name="app">The application to edit.</param>
        /// <param name="bundles">The bundles to show.</param>
        /// <param name="languages">The languages to show.</param>
        public WinResourcesGrid(RBApplication app, ObservableSortedDictionary<string, string> bundles, ObservableSortedDictionary<string, string> languages) {
            InitializeComponent();
            this.bundles = bundles;
            this.languages = languages;
            this.application = app;

            resourceCache = new SortedDictionary<int, string>();
            StringBuilder sqlBuffer = new StringBuilder();
            string bundleFormat = null,
                   crossTabFormat = null,
                   crossTabEnd = null;
            if(db == null) {
                db = new Database();
            }
            switch(db.InstanceType) {
                case Database.DatabaseType.MySQL:
                case Database.DatabaseType.SQLServer:
                    bundleFormat = "'{0}', ";
                    break;
                case Database.DatabaseType.SQLite:
                    bundleFormat = "\"{0}\", ";
                    break;
            }
            foreach(KeyValuePair<string, string> bundle in bundles) {
                sqlBuffer.AppendFormat(bundleFormat, bundle.Key);
            }
            sqlBuffer.Remove(sqlBuffer.Length - 2, 1); // remove last comma
            string bundleSql = sqlBuffer.ToString();
            sqlBuffer.Remove(0, sqlBuffer.Length);

            switch(db.InstanceType) {
                case Database.DatabaseType.MySQL:
                    crossTabFormat = "MAX(IF(language='{0}',translation,null)) AS '{0}',";
                    resourceSql = @"SELECT DISTINCT resourceid, 
                                        CAST(IF(CHAR_LENGTH(translation) < 20, 
                                            CONCAT(resourceid, ' - ',translation), 
                                            CONCAT(resourceid, ' - ',SUBSTRING(translation,1,17),'...')
                                         ) AS CHAR) AS display
                                        FROM " + db.TablePrefix + "resources WHERE language = @defaultLang ORDER BY resourceid ASC";
                    crossTabEnd = string.Format(@" FROM {0}elements AS e
                            INNER JOIN {0}resources AS r ON r.resourceid = e.resourceid 
                            LEFT JOIN {0}bundles AS b ON b.id = e.bundle
                            LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid 
                            WHERE applicationid = ?pID AND bundle IN ({1}) 
                            GROUP BY e.id 
                            ORDER BY bundle, elementname ", db.TablePrefix, bundleSql);
                    break;
                case Database.DatabaseType.SQLServer:
                    crossTabFormat = "MAX(CASE WHEN language = '{0}' THEN translation ELSE NULL END) AS '{0}',";
                    resourceSql = @"SELECT DISTINCT resourceid, display = CAST(resourceid AS VARCHAR)+' - '+
                                        CASE
                                         WHEN LEN(translation) < 20 THEN translation
                                         ELSE SUBSTRING(translation,1,17)+'...'
                                        END
                                        FROM " + db.TablePrefix + "resources WHERE language = @defaultLang ORDER BY resourceid ASC";
                    crossTabEnd = string.Format(@" FROM {0}elements AS e
                            INNER JOIN {0}resources AS r ON r.resourceid = e.resourceid
                            LEFT JOIN {0}bundles AS b ON b.id = e.bundle
                            LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid
                            WHERE applicationid = @pID  AND bundle IN ({1}) 
                            GROUP BY e.id,e.resourceid,applicationid,bundle,elementname,e.comment,c.comment,b.name
                            ORDER BY bundle, elementname", db.TablePrefix, bundleSql);
                    break;
                case Database.DatabaseType.SQLite:
                    crossTabFormat = "MAX(CASE WHEN language = '{0}' THEN translation ELSE NULL END) AS '{0}',";
                    resourceSql = @"SELECT DISTINCT resourceid, (CAST(resourceid AS VARCHAR) || ' - '||
                                    CASE
                                     WHEN LENGTH(translation) < 20 THEN translation
                                     ELSE SUBSTR(translation,1,17)||'...'
                                    END) AS display 
                                    FROM " + db.TablePrefix + "resources WHERE language = @defaultLang ORDER BY resourceid ASC";
                    crossTabEnd = string.Format(@" FROM {0}elements AS e
                            INNER JOIN {0}resources AS r ON r.resourceid = e.resourceid
                            LEFT JOIN {0}bundles AS b ON b.id = e.bundle
                            LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid
                            WHERE applicationid = @pID  AND bundle IN ({1}) 
                            GROUP BY e.id,e.resourceid,applicationid,bundle,elementname,e.comment,c.comment,b.name
                            ORDER BY bundle, elementname", db.TablePrefix, bundleSql);
                    break;
            }
            GridViewColumn col;
            string defaultLanguageName = null;
            try {
                defaultLanguageName = CultureInfo.GetCultureInfo(application.DefaultLanguage).EnglishName;
            } catch {
                defaultLanguageName = application.DefaultLanguage;
            }
            sqlBuffer.AppendFormat(crossTabFormat, application.DefaultLanguage);
            col = new GridViewColumn();
            col.Header = defaultLanguageName;
            col.DisplayMemberBinding = new Binding(application.DefaultLanguage);
            col.Width = 120;
            //todo: tooltip?
            grdResources.Columns.Add(col);

            foreach(KeyValuePair<string, string> language in languages) {
                if(language.Key == application.DefaultLanguage)
                    continue;
                sqlBuffer.AppendFormat(crossTabFormat, language.Key);
                col = new GridViewColumn();
                col.Header = language.Value;
                col.DisplayMemberBinding = new Binding(language.Key);
                col.Width = 120;
                //todo: tooltip?
                grdResources.Columns.Add(col);
            }
            sqlBuffer.Remove(sqlBuffer.Length - 1, 1); // remove last comma
            crossTabColumns = sqlBuffer.ToString();

            sqlBuffer.Insert(0, @"SELECT e.id, applicationid, bundle, elementname, e.resourceid, e.comment AS elementcomment, c.comment as resourcecomment, b.name AS bundlename,");
            sqlBuffer.AppendFormat(crossTabEnd);
            crossTabSql = sqlBuffer.ToString();

            RefreshResources();
        }

        /// <summary>
        /// Refreshes the grid of resources.
        /// </summary>
        private void RefreshResources() {
            BackgroundWorker worker = new BackgroundWorker();
            WinProgress progress = new WinProgress("Loading resources...", true);
            worker.DoWork += (s, dwe) => {
                try {
                    if(db == null || !db.Connected) {
                        db = new Database();
                        db.OpenConnection();
                    }
                    //FilterGrid();
                    using(Database.Command cmd = db.CreateCommand()) {
                        DataTable dtResources = new DataTable();
                        cmd.CommandText = crossTabSql;
                        cmd.AddParameterWithValue("@pID", application.ID);
                        Database.DataAdapter adapter = new Database.DataAdapter(cmd);
                        adapter.Fill(dtResources);
                        dwe.Result = dtResources;

                        resourceCache.Clear();
                        cmd.CommandText = resourceSql;
                        cmd.AddParameterWithValue("@defaultLang", application.DefaultLanguage);
                        DbDataReader reader = cmd.ExecuteReader();
                        resourceCache.Add(-1, "New Resource");
                        while(reader.Read()) {
                            resourceCache.Add(int.Parse(reader["resourceid"].ToString()), reader["display"].ToString());
                        }
                        reader.Close();
                    }
                } catch(Exception ex) {
                    dwe.Result = ex.Message;
                } finally {
                    if(db != null) {
                        try {
                            db.CloseConnection();
                        } catch { }
                    }
                }
            };
            worker.RunWorkerCompleted += (s, rwe) => {
                progress.Close();
                if(rwe.Result is DataTable) {
                    DataContext = rwe.Result as DataTable;
                } else {
                    MessageBox.Show("Error: Could not connect to database - " + rwe.Result.ToString(), "Database Error", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            };
            progress.Show();
            worker.RunWorkerAsync();
        }

        /// <summary>
        /// Shows a window to add a new element.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.ExecutedRoutedEventArgs"/> instance containing the event data.</param>
        private void AddElement(object sender, ExecutedRoutedEventArgs e) {
            RBElement element = new RBElement();
            element.ApplicationID = application.ID;
            element.BundleID = bundles.First().Key;
            element.BundleName = bundles.First().Value;
            RBResource res = new RBResource();
            foreach(KeyValuePair<string, string> lang in languages) {
                res.Translations.Add(lang.Key, new RBTranslation());
            }
            element.Resource = res;
            WinAddElement dlg = new WinAddElement(bundles, resourceCache, crossTabColumns, element, application.DefaultLanguage);
            dlg.ResourcesChanged += () => { RefreshResources(); };
            dlg.Show();
        }

        /// <summary>
        /// Determines whether the edit element command can be executed.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.CanExecuteRoutedEventArgs"/> instance containing the event data.</param>
        private void CanExecuteEditElement(object sender, CanExecuteRoutedEventArgs e) {
            e.CanExecute = lstGrid != null && lstGrid.SelectedItem != null;
            e.Handled = true;
        }

        /// <summary>
        /// Shows a window to edit an element.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.ExecutedRoutedEventArgs"/> instance containing the event data.</param>
        private void EditElement(object sender, ExecutedRoutedEventArgs e) {
            if(lstGrid.SelectedItem == null) {
                return;
            }
            DataRow row = (lstGrid.SelectedItem as DataRowView).Row;
            DataColumnCollection cols = row.Table.Columns; // this is tightly linked with the crosstab query above!
            object[] elementData = row.ItemArray;
            RBElement selectedElement = new RBElement(int.Parse(elementData[0].ToString()), elementData[3].ToString(), elementData[1].ToString(), elementData[2].ToString(), elementData[7].ToString(), elementData[5].ToString());
            // This element does not include the translators comments as retrieving them requires another query.
            // This is only executed if the user decides to edit the resource used for the element
            RBResource res = new RBResource(int.Parse(elementData[4].ToString()), elementData[6].ToString());
            for(int i = 8; i < elementData.Length; i++) {
                res.Translations.Add(cols[i].ColumnName, new RBTranslation(elementData[i].ToString(), ""));
            }
            selectedElement.Resource = res;
            WinAddElement dlg = new WinAddElement(bundles, resourceCache, crossTabColumns, selectedElement, application.DefaultLanguage);
            dlg.ResourcesChanged += () => { RefreshResources(); };
            dlg.Show();
        }

        /// <summary>
        /// Determines whether the delete element command can be executed.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.CanExecuteRoutedEventArgs"/> instance containing the event data.</param>
        private void CanExecuteDeleteElement(object sender, CanExecuteRoutedEventArgs e) {
            e.CanExecute = lstGrid != null && lstGrid.SelectedItem != null;
            e.Handled = true;
        }

        /// <summary>
        /// Shows a prompt to confirm deletion of an element, then deletes it from the database.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.ExecutedRoutedEventArgs"/> instance containing the event data.</param>
        private void DeleteElement(object sender, ExecutedRoutedEventArgs e) {
            if(lstGrid.SelectedItem == null) {
                return;
            }
            object[] elementData = (lstGrid.SelectedItem as DataRowView).Row.ItemArray; // this is tightly linked with the crosstab query above!
            string elementName = elementData[3].ToString(),
                   elementId = elementData[0].ToString();
            if(MessageBox.Show(this, string.Format("The element '{0}' wil be deleted. Any resources or translations will be left intact.{1}{1}Are you sure?", elementName, Environment.NewLine), "Confirm Delete", MessageBoxButton.YesNo, MessageBoxImage.Warning, MessageBoxResult.No) == MessageBoxResult.Yes) {
                BackgroundWorker worker = new BackgroundWorker();
                WinProgress progress = new WinProgress("Deleting element...", true);
                worker.DoWork += (s, dwe) => {
                    try {
                        if(db == null || !db.Connected) {
                            db = new Database();
                            db.OpenConnection();
                        }
                        using(Database.Command cmd = db.CreateCommand()) {
                            cmd.CommandText = "DELETE FROM " + db.TablePrefix + "elements WHERE applicationid = @pID AND id = @eID";
                            cmd.AddParameterWithValue("@pID", application.ID);
                            cmd.AddParameterWithValue("@eID", elementId);
                            cmd.ExecuteNonQuery();
                        }
                    } catch(Exception ex) {
                        dwe.Result = ex.Message;
                    } finally {
                        if(db != null) {
                            try {
                                db.CloseConnection();
                            } catch { }
                        }
                    }
                };
                worker.RunWorkerCompleted += (s, rwe) => {
                    progress.Close();
                    if(rwe.Result == null) {
                        RefreshResources();
                    } else {
                        MessageBox.Show("Error: Could not connect to database - " + rwe.Result.ToString(), "Database Error", MessageBoxButton.OK, MessageBoxImage.Error);
                    }
                };
                progress.Show();
                worker.RunWorkerAsync();
            }
        }

        /// <summary>
        /// Executes the ApplicationCommands.Properties command 
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.Windows.Input.MouseButtonEventArgs"/> instance containing the event data.</param>
        private void lstGrid_MouseDoubleClick(object sender, MouseButtonEventArgs e) {
            ApplicationCommands.Properties.Execute(null, this);
        }

        /// <summary>
        /// Closes the window.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.ExecutedRoutedEventArgs"/> instance containing the event data.</param>
        private void CloseWindow(object sender, ExecutedRoutedEventArgs e) {
            Close();
        }
    }
}
